﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BL.Entity;
using DL;
using System.Transactions;
using System.Data.SqlClient;

namespace BL.Service
{
    public class tbl_NumberServer : ServiceBase
    {
        public void ClearTable()
        {
            this.Context.ExecuteStoreCommand("TRUNCATE TABLE tbl_number");
        }
        public void DeleteNumber(string number, decimal over_price, decimal under_price, decimal tod_price, string numType, string UserCode)
        {


            #region :: Check Num Input ::

            try
            {

                number = number.Replace("+", "");
                int checknum = int.Parse(number.Replace("*", "").Replace("/", "").Replace("-", ""));
                if (checknum.ToString().Length > 6) { throw new Exception(string.Format("ไม่สามารถใส่ข้อมูลได้ {0}", number)); }

            }
            catch
            {

                throw new Exception(string.Format("ป้อนข้อมูลผิดพลาด {0}", number));
            }

            #endregion

            List<NumberDTO> addNumber = new List<NumberDTO>();
            NumberDTO dto = new NumberDTO()
            {
                CreateBy = UserCode,
                CreateDate = GetDateGobal(),
                UpdateBy = UserCode,
                UpdateDate = GetDateGobal(),
                Number = number,
                Over_Price = over_price,
                Tod_Price = tod_price,
                Under_Price = under_price,
                NumType = numType

            };


            #region :: Check num Input ::
            if (number.Length == 2 && number.Contains('/'))
            {
                //ถ้ากรอกที่ช่องบน แสดง "2 บนเต็ง(19)"
                //ถ้ากรอกที่ช่องล่าง แสดง "2 ล่างเต็ง(19)"



                addNumber.AddRange(GetX19(dto));


            }
            else if (number.Contains('*'))
            {
                string numCheck = dto.Number.Replace("*", "");

                //ถ้ากรอกที่ช่องบน เลข 3 ตัวไม่ซ้ำกัน ตามด้วย (*) แสดง "3 บนเต็ง(6)"              

                addNumber.AddRange(SwitchNum_star(dto));
            }
            else
            {
                //ถ้ากรอกที่ช่องบน เลข 3 ตัว ไม่ใส่ช่องโต๊ด แสดง "3 บนเต็ง"
                //ถ้ากรอกที่ช่องบน เลข 3 ตัว ใส่ช่องโต๊ด แสดง "3 บนเต็ง,โต๊ด"
                //ถ้ากรอกที่ช่องล่าง เลข 3 ตัว ไม่ใส่ช่องโต๊ด แสดง "3 ล่างเต็ง" 

                addNumber.Add(dto);
            }
            #endregion

            TransactionOptions tranOpt = new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted };
            using (var trans = new TransactionScope(TransactionScopeOption.Required, tranOpt))
            {
                try
                {
                    foreach (NumberDTO deleteitem in addNumber)
                    {
                        var query = from item in this.Context.tbl_number
                                    where
                                        deleteitem.Number == item.Number && deleteitem.NumType == item.NumType
                                    select item;
                        tbl_number delete = query.FirstOrDefault();
                        tbl_number newAddNum = new tbl_number();
                        if (delete != null)
                        {
                            newAddNum.Number = delete.Number;
                            newAddNum.NumType = delete.NumType;

                            newAddNum.Over_Price = query.Sum(s => s.Over_Price);
                            newAddNum.Under_Price = query.Sum(s => s.Under_Price);
                            newAddNum.Tod_Price = query.Sum(s => s.Tod_Price);

                            if (over_price > newAddNum.Over_Price) { newAddNum.Over_Price = 0; }
                            else { newAddNum.Over_Price = newAddNum.Over_Price - over_price; }

                            if (under_price > newAddNum.Under_Price) { newAddNum.Under_Price = 0; }
                            else { newAddNum.Under_Price = newAddNum.Under_Price - under_price; }

                            if (tod_price > newAddNum.Tod_Price) { newAddNum.Tod_Price = 0; }
                            else { newAddNum.Tod_Price = newAddNum.Tod_Price - tod_price; }



                            foreach (tbl_number deleteFromDBitem in query.ToList())
                            {

                                this.Context.DeleteObject(deleteFromDBitem);

                            }

                            if (delete != null && newAddNum.Over_Price + newAddNum.Under_Price + newAddNum.Tod_Price > 0)
                            {
                                tbl_number newAddNum2 = new tbl_number()
                                {
                                    CreateBy = deleteitem.CreateBy,
                                    CreateDate = deleteitem.CreateDate,
                                    Number = deleteitem.Number,
                                    NumType = deleteitem.NumType,
                                    Over_Price = newAddNum.Over_Price,
                                    Under_Price = newAddNum.Under_Price,
                                    Tod_Price = newAddNum.Tod_Price,
                                    UpdateBy = deleteitem.UpdateBy,
                                    UpdateDate = deleteitem.UpdateDate
                                };
                                this.Context.AddTotbl_number(newAddNum);
                            }

                        }
                    }

                    this.Context.SaveChanges();
                    trans.Complete();
                }
                catch (Exception)
                {
                    trans.Dispose();
                }
            }
        }
        public string Addnumber(string number, decimal over_price, decimal under_price, decimal tod_price, string UserName, string numType, bool IsDeleteNum)
        {
            string command = string.Empty;
            if (IsDeleteNum && (numType == Enum.NumberType.Tod2 || numType == Enum.NumberType.Tod3 || numType == Enum.NumberType.FiveNumber))
            {
                number = number.Replace("*", "");
            }
            if (over_price == 0 && under_price == 0 && tod_price == 0) { return string.Empty; }

            #region :: Check Num Input ::

            try
            {

                number = number.Replace("+", "");
                int checknum = int.Parse(number.Replace("*", "").Replace("/", "").Replace("-", ""));
                if (number.Length > 7) { throw new Exception(string.Format("ไม่สามารถใส่ข้อมูลได้ {0}", number)); }

            }
            catch
            {

                throw new Exception(string.Format("ป้อนข้อมูลผิดพลาด {0}", number));
            }

            #endregion

            List<NumberDTO> addNumber = new List<NumberDTO>();
            NumberDTO dto = new NumberDTO()
            {
                CreateBy = UserName,
                CreateDate = GetDateGobal(),
                UpdateBy = UserName,
                UpdateDate = GetDateGobal(),
                Number = number,
                Over_Price = over_price,
                Tod_Price = tod_price,
                Under_Price = under_price,
                NumType = numType

            };


            #region :: Check num Input ::
            if (number.Length == 2 && number.Contains('/'))
            {
                //ถ้ากรอกที่ช่องบน แสดง "2 บนเต็ง(19)"
                //ถ้ากรอกที่ช่องล่าง แสดง "2 ล่างเต็ง(19)"
                #region :: num is 19 door ::

                switch (numType)
                {
                    case Enum.NumberType.NumberOverAdd:
                        command = "2 บนเต็ง(19)";
                        break;
                    case Enum.NumberType.NumberUnderAdd:
                        command = "2 ล่างเต็ง(19)";
                        break;
                }
                addNumber.AddRange(GetX19(dto));

                #endregion
            }
            else if (number.Contains('*'))
            {
                #region :: Get command for star ::

                string numCheck = dto.Number.Replace("*", "");
                if (numCheck.Length == 3)
                {
                    switch (numType)
                    {
                        case Enum.NumberType.NumberOverAdd:
                            switch (numCheck.GroupBy(n => n).Count())
                            {
                                case 1:
                                    break;
                                case 2:
                                    command = "3 บนเต็ง(3)";
                                    break;
                                case 3:
                                    command = "3 บนเต็ง(6)";
                                    break;
                            }
                            break;

                        case Enum.NumberType.NumberUnderAdd:
                            switch (numCheck.GroupBy(n => n).Count())
                            {
                                case 1:
                                    break;
                                case 2:
                                    command = "3 ล่างเต็ง(3)";
                                    break;
                                case 3:
                                    command = "3 ล่างเต็ง(6)";
                                    break;
                            }
                            break;
                    }
                #endregion

                }
                else if (numCheck.Length == 2)
                {

                    #region :: Get command for langth ::
                    switch (numType)
                    {
                        case Enum.NumberType.NumberOverAdd:
                            switch (numCheck.GroupBy(n => n).Count())
                            {
                                case 1:
                                    command = "2 บน,หน้า,ถ่าง";
                                    break;
                                case 2:
                                    command = "2 บน,หน้า,ถ่าง";
                                    break;
                            }
                            break;
                    }
                    #endregion

                }
                else if (numCheck.Length == 4)
                {

                    command = "3 บนเต็ง(24)";
                }
                else if (numCheck.Length == 5)
                {
                    command = "3 บนเต็ง(60)";
                }
                else if (numCheck.Length == 6)
                {
                    command = "3 บนเต็ง(120)";
                }
                //ถ้ากรอกที่ช่องบน เลข 3 ตัวไม่ซ้ำกัน ตามด้วย (*) แสดง "3 บนเต็ง(6)"              

                addNumber.AddRange(SwitchNum_star(dto));
            }
            else
            {
                //ถ้ากรอกที่ช่องบน เลข 3 ตัว ไม่ใส่ช่องโต๊ด แสดง "3 บนเต็ง"
                //ถ้ากรอกที่ช่องบน เลข 3 ตัว ใส่ช่องโต๊ด แสดง "3 บนเต็ง,โต๊ด"
                //ถ้ากรอกที่ช่องล่าง เลข 3 ตัว ไม่ใส่ช่องโต๊ด แสดง "3 ล่างเต็ง" 
                #region :: get 3Num for langth ::
                if (dto.Number.Length == 3)
                {
                    if (!dto.Number.Contains("-"))
                    {
                        switch (numType)
                        {
                            case Enum.NumberType.NumberOverAdd:
                                if (dto.Over_Price > 0 && dto.Tod_Price == 0)
                                {
                                    command = "3 บนเต็ง";
                                }
                                else if (dto.Tod_Price > 0 && dto.Over_Price > 0)
                                {
                                    command = "3 บนเต็ง,โต๊ด";
                                }
                                else if (dto.Over_Price == 0 && dto.Tod_Price > 0)
                                {
                                    command = "3 บนโต๊ด ";
                                }

                                break;
                            case Enum.NumberType.NumberUnderAdd:
                                if (dto.Under_Price > 0)
                                {
                                    command = "3 ล่างเต็ง";
                                }

                                break;
                        }
                    }
                    else if (dto.Number[1] == '-' && dto.Number.Count(c => c == '-') == 1)
                    {
                        command = "2 ตัวบนคู่ถ่าง";
                    }
                    else if (dto.Number[2] == '-' && dto.Number.Count(c => c == '-') == 1)
                    {
                        command = "2 ตัวบนคู่หน้า";
                    }
                    else if (dto.Number.Where(n => n == '-').Count() == 2)
                    {
                        if (dto.Number[0] == '-' && dto.Number[1] == '-')
                        {
                            command = "บน หลักหน่วย";
                        }
                        else if (dto.Number[0] == '-' && dto.Number[2] == '-')
                        {
                            command = "บน หลักสิบ";
                        }
                        else if (dto.Number[1] == '-' && dto.Number[2] == '-')
                        {
                            command = "บน หลักร้อย";
                        }
                    }
                #endregion

                }
                else if (dto.Number.Length == 2)
                {
                    #region :: get 2num for langth ::
                    if (!dto.Number.Contains("-"))
                    {
                        switch (numType)
                        {
                            case Enum.NumberType.NumberOverAdd:
                                if (dto.Tod_Price == 0)
                                {
                                    command = "2 บนเต็ง";
                                }
                                else if (dto.Tod_Price > 0 && dto.Over_Price > 0)
                                {
                                    command = "2 บนเต็ง,โต๊ด";
                                }
                                else if (dto.Over_Price == 0)
                                {
                                    command = "2 บนโต๊ด ";
                                }

                                break;
                            case Enum.NumberType.NumberUnderAdd:
                                if (dto.Under_Price > 0)
                                {
                                    command = "2 ล่างเต็ง";
                                }

                                break;
                        }
                    }
                    else if (dto.Number.Contains("-"))
                    {
                        if (dto.Number[0] == '-')
                        {
                            command = "ล่าง หลักหน่วย";
                        }
                        else if (dto.Number[1] == '-')
                        {
                            command = "ล่าง หลักสิบ";
                        }
                    }

                    #endregion
                }
                else if (dto.Number.Length == 1)
                {
                    #region :: get 1Num for langth ::
                    if (!dto.Number.Contains("-") && !dto.Number.Contains("/") && !dto.Number.Contains("*"))
                    {
                        switch (numType)
                        {
                            case Enum.NumberType.NumberOverAdd:
                                command = "วิ่งบน";
                                break;
                            case Enum.NumberType.NumberUnderAdd:
                                command = "วิ่งล่าง";
                                break;
                        }

                    }
                    #endregion

                }
                else if (dto.Number.Length == 4)
                {
                    #region :: get 4Num for langth ::
                    if (!dto.Number.Contains("-") && !dto.Number.Contains("/") && !dto.Number.Contains("*"))
                    {
                        if (dto.Over_Price == 0 && dto.Tod_Price > 0)
                        {
                            command = "พวง 4 ตัว";
                        }
                    }
                    #endregion
                }
                else if (dto.Number.Length == 5)
                {
                    #region :: get 5Num for langth ::
                    if (!dto.Number.Contains("-") && !dto.Number.Contains("/") && !dto.Number.Contains("*"))
                    {
                        if (dto.Over_Price == 0 && dto.Tod_Price > 0)
                        {
                            command = "5 บนโต๊ด";
                        }
                    }
                    #endregion
                }

                addNumber.Add(dto);
            }
            #endregion



            #region :: Open Transectio Database ::

            TransactionOptions tranOpt = new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted };
            using (var trans = new TransactionScope(TransactionScopeOption.Required, tranOpt))
            {
                try
                {
                    #region Transaction Scope
                    string numTypeForDel = numType;
                    if (IsDeleteNum && (numTypeForDel == Enum.NumberType.Tod2 || numTypeForDel == Enum.NumberType.Tod3 || numTypeForDel == Enum.NumberType.FiveNumber || numTypeForDel == Enum.NumberType.FourNumber))
                    {
                        List<tbl_number> query;
                        string num1, num2, num3, num4, num5;

                        if (numTypeForDel == Enum.NumberType.Tod2)
                        {
                            num1 = number[0].ToString();
                            num2 = number[1].ToString();
                            query = (from numdel in this.Context.tbl_number
                                     where numdel.Number.Contains(num1)
                                     && numdel.Number.Contains(num2)
                                     && numdel.NumType == BL.Enum.NumberType.NumberOverAdd
                                     && numdel.Tod_Price > 0
                                     select numdel).ToList();
                        }
                        else if (numTypeForDel == Enum.NumberType.Tod3)
                        {
                            num1 = number[0].ToString();
                            num2 = number[1].ToString();
                            num3 = number[2].ToString();
                            query = (from numdel in this.Context.tbl_number
                                     where numdel.Number.Contains(num1)
                                     && numdel.Number.Contains(num2)
                                     && numdel.Number.Contains(num3)
                                     && numdel.NumType == BL.Enum.NumberType.NumberOverAdd
                                     && numdel.Tod_Price > 0
                                     select numdel).ToList();
                        }
                        else if (number.Length == 4)
                        {
                            num1 = number[0].ToString();
                            num2 = number[1].ToString();
                            num3 = number[2].ToString();
                            num4 = number[3].ToString();
                            query = (from numdel in this.Context.tbl_number
                                     where numdel.Number.Contains(num1)
                                     && numdel.Number.Contains(num2)
                                     && numdel.Number.Contains(num3)
                                     && numdel.Number.Contains(num4)
                                     && numdel.NumType == BL.Enum.NumberType.NumberOverAdd
                                     && numdel.Tod_Price > 0
                                     select numdel).ToList();

                        }
                        else
                        {
                            num1 = number[0].ToString();
                            num2 = number[1].ToString();
                            num3 = number[2].ToString();
                            num4 = number[3].ToString();
                            num5 = number[4].ToString();
                            query = (from numdel in this.Context.tbl_number
                                     where numdel.Number.Contains(num1)
                                     && numdel.Number.Contains(num2)
                                     && numdel.Number.Contains(num3)
                                     && numdel.Number.Contains(num4)
                                     && numdel.Number.Contains(num5)
                                     && numdel.NumType == BL.Enum.NumberType.NumberOverAdd
                                     && numdel.Tod_Price > 0
                                     select numdel).ToList();

                        }
                        if (query != null && query.Count > 0)
                        {
                            tbl_number updateNum;
                            List<tbl_number> updateFromDB = query.Where(w => w.Number != number).ToList();
                            updateNum = query.Where(w => w.Number == number).FirstOrDefault();
                            if (updateNum != null)
                            {
                                if (query.Sum(s => s.Tod_Price) >= over_price)
                                {
                                    updateNum.Tod_Price = query.Sum(s => s.Tod_Price) - over_price;
                                }
                                else
                                {
                                    throw new Exception("จำนวนเงินที่ต้องการลบมีมากกว่าจำนวนเงินในระบบ");
                                }

                            }
                            if (updateFromDB != null && updateFromDB.Count > 0)
                            {
                                tbl_number updatePrice = new tbl_number();
                                updatePrice.UpdateBy = UserName;
                                updatePrice.UpdateDate = GetDateGobal();
                                updatePrice.CreateBy = updateFromDB.FirstOrDefault().CreateBy;
                                updatePrice.CreateDate = updateFromDB.FirstOrDefault().CreateDate;
                                updatePrice.Number = updateFromDB.FirstOrDefault().Number;
                                updatePrice.NumType = updateFromDB.FirstOrDefault().NumType;
                                updatePrice.Over_Price = updateFromDB.Sum(o => o.Over_Price);
                                updatePrice.Under_Price = updateFromDB.Sum(u => u.Under_Price);
                                updatePrice.Tod_Price = updateFromDB.Sum(o => o.Tod_Price) - over_price;
                                this.Context.AddTotbl_number(updatePrice);
                                foreach (tbl_number Updateitem in updateFromDB)
                                {
                                    this.Context.DeleteObject(Updateitem);

                                }
                            }
                        }
                        else
                        {
                            throw new Exception("ไม่มีข้อมูลที่ท่านต้องการลบ");
                        }


                    }
                    else
                    {
                        #region :: Loop for Add Or Delete num not Tod ::

                        foreach (NumberDTO num in addNumber)
                        {
                            if (IsDeleteNum)
                            {

                                #region :: Delete Num Not Tod::
                                string DBNumType = this.ConvertNumTypeForDeleteToNumTypeINDB(numTypeForDel);
                                var query = from item in this.Context.tbl_number
                                            where item.Number == num.Number && item.NumType == DBNumType
                                            select item;
                                tbl_number updateNum = query.FirstOrDefault();
                                if (updateNum != null)
                                {
                                    updateNum.Over_Price = query.Sum(o => o.Over_Price);
                                    updateNum.Under_Price = query.Sum(u => u.Under_Price);
                                    updateNum.Tod_Price = query.Sum(t => t.Tod_Price);

                                    #region :: Check numtype ::
                                    switch (numTypeForDel)
                                    {
                                        case Enum.NumberType.Over3:
                                            updateNum.Over_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;

                                        case Enum.NumberType.Over2:

                                            updateNum.Over_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;

                                        case Enum.NumberType.Under3:

                                            updateNum.Under_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberUnderAdd;
                                            break;
                                        case Enum.NumberType.Under2:

                                            updateNum.Under_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberUnderAdd;
                                            break;
                                        case Enum.NumberType.TwinFront:
                                            updateNum.Over_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                        case Enum.NumberType.TwinStretch:
                                            updateNum.Over_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                        case Enum.NumberType.Under1:
                                            updateNum.Under_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberUnderAdd;
                                            break;
                                        case Enum.NumberType.Under1Decimal:
                                            updateNum.Under_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberUnderAdd;
                                            break;
                                        case Enum.NumberType.Under1unit:
                                            updateNum.Under_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberUnderAdd;
                                            break;
                                        case Enum.NumberType.Over1:
                                            updateNum.Over_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                        case Enum.NumberType.Over1Unit:
                                            updateNum.Over_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                        case Enum.NumberType.Over1Decimal:
                                            updateNum.Over_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                        case Enum.NumberType.Over1Hundet:
                                            updateNum.Over_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                        case Enum.NumberType.FourNumber:
                                            updateNum.Tod_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                        case Enum.NumberType.Tod2:
                                            updateNum.Tod_Price -= num.Cut_Out;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                        case Enum.NumberType.Tod3:
                                            updateNum.Tod_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                        case Enum.NumberType.FiveNumber:
                                            updateNum.Tod_Price -= over_price;
                                            updateNum.NumType = BL.Enum.NumberType.NumberOverAdd;
                                            break;
                                    }
                                    #endregion

                                    #region :: Check Error ::

                                    if (updateNum.Over_Price < 0 || updateNum.Under_Price < 0 || updateNum.Tod_Price < 0)
                                    {
                                        throw new Exception(string.Format("ไม่สามารถลบ {0} ได้เนื่องจากผลของการลบเป็นค่าติดลบ", number));
                                    }

                                    #endregion


                                    foreach (tbl_number delItem in query.ToList())
                                    {
                                        this.Context.DeleteObject(delItem);
                                    }

                                    if (updateNum.Over_Price + updateNum.Under_Price + updateNum.Tod_Price > 0)
                                    {
                                        tbl_number newObj = new tbl_number()
                                        {
                                            Number = updateNum.Number,
                                            NumType = updateNum.NumType,
                                            Over_Price = updateNum.Over_Price,
                                            Under_Price = updateNum.Under_Price,
                                            Tod_Price = updateNum.Tod_Price,
                                            CreateBy = updateNum.CreateBy,
                                            CreateDate = updateNum.CreateDate,
                                            UpdateBy = UserName,
                                            UpdateDate = DateTime.Now
                                        };
                                        this.Context.AddTotbl_number(newObj);
                                    }

                                }
                                else
                                {
                                    throw new Exception("ไม่มีข้อมูลที่ท่านต้องการลบ");
                                }
                                #endregion

                            }
                            else
                            {
                                tbl_number numberAddDB = new tbl_number()
                                {

                                    CreateBy = dto.CreateBy,
                                    CreateDate = dto.CreateDate,
                                    Number = num.Number,
                                    NumType = numType,
                                    Over_Price = num.Over_Price,
                                    Tod_Price = num.Tod_Price,
                                    Under_Price = num.Under_Price,
                                    UpdateBy = dto.CreateBy,
                                    UpdateDate = dto.CreateDate
                                };
                                this.Context.AddTotbl_number(numberAddDB);

                            }


                        }

                        #endregion
                    }
                    this.Context.SaveChanges();
                    trans.Complete();
                    #endregion
                }
                catch (Exception ex)
                {
                    trans.Dispose();
                    throw ex;
                }
                finally
                {
                    trans.Dispose();
                }
            }

            #endregion

            return command;
        }
        public string ConvertNumTypeForDeleteToNumTypeINDB(string numTypeForDel)
        {
            string result = string.Empty;

            switch (numTypeForDel)
            {
                case Enum.NumberType.Over3:
                case Enum.NumberType.Over2:
                case Enum.NumberType.TwinFront:
                case Enum.NumberType.Over1:
                case Enum.NumberType.Over1Unit:
                case Enum.NumberType.Over1Decimal:
                case Enum.NumberType.Over1Hundet:
                case Enum.NumberType.FourNumber:
                case Enum.NumberType.Tod2:
                case Enum.NumberType.Tod3:
                case Enum.NumberType.FiveNumber:
                case Enum.NumberType.TwinStretch:

                    result = BL.Enum.NumberType.NumberOverAdd;
                    break;

                case Enum.NumberType.Under3:
                case Enum.NumberType.Under2:
                case Enum.NumberType.Under1:
                case Enum.NumberType.Under1Decimal:
                case Enum.NumberType.Under1unit:
                    result = BL.Enum.NumberType.NumberUnderAdd;
                    break;

            }
            return result;
        }
        public string showWord(string number, decimal over_price, decimal under_price, decimal tod_price, string result, int check)
        {
            NumberDTO dto = new NumberDTO()
            {
                Number = number,
                Over_Price = over_price,
                Tod_Price = tod_price,
                Under_Price = under_price,
                //NumType = numType
            };

            string command = string.Empty;
            if (over_price == 0 && under_price == 0 && tod_price == 0) { return string.Empty; }
            string[] resultLB = result.Split(',');
            List<string> Tokens = new List<string>();
            foreach (string word in resultLB)
            {
                Tokens.Add(word);
            }
            string[] words = Tokens[0].Split(' ');
            if (check == 0)
            {
                if (over_price == 0 && tod_price > 0)
                {
                    if (number.Length == 2)
                    {
                        command = "แก้ไข โต๊ด 2 บน " + " ( " + number + " = " + tod_price + " ) ";
                    }
                    else if (number.Length == 3)
                    {
                        command = "แก้ไข โต๊ด 3 บน " + " ( " + number + " = " + tod_price + " ) ";
                    }
                    else if (number.Length == 4)
                    {
                        command = "แก้ไข  " + result + " ( " + number + " = " + tod_price + " ) ";
                    }
                    else if (number.Length == 5)
                    {
                        command = "แก้ไข โต๊ด 5 บนหมู่  " + " ( " + number + " = " + tod_price + " ) ";
                    }
                }
                else if (over_price > 0 && tod_price == 0)
                {
                    if (number.Length == 2 || number.Length == 2 && number.Contains('/'))
                    {
                        command = "แก้ไข  2 ตัวบน" + " ( " + number + " = " + over_price + " )";
                    }
                    else if (number.Contains('*'))
                    {
                        string numCheck = number.Replace("*", "");
                        if (numCheck.Length == 2)
                        {
                            command = "แก้ไข  2 ตัวบน, 2 บนคู่หน้า, 2 บนคู่ถ่าง " + " ( " + number + " = " + over_price + " )";
                        }
                        else if (numCheck.Length == 3 || numCheck.Length == 4 || numCheck.Length == 5)
                        {
                            command = "แก้ไข  3 ตัวบน " + " ( " + number + " = " + over_price + " )";
                        }
                    }
                    else if (number.Contains('-'))
                    {
                        string numCheck = number.Replace("-", "");
                        if (numCheck.Length == 2)
                        {
                            command = "แก้ไข  " + result + " ( " + number + " = " + over_price + " )";
                        }
                        else if (numCheck.Length == 1)
                        {
                            command = "แก้ไข  " + result + " ( " + numCheck + " = " + over_price + " )";
                        }

                    }
                    else
                    {
                        command = "แก้ไข  " + result + " ( " + number + " = " + over_price + " )";
                    }
                }
                else if (over_price > 0 && tod_price > 0)
                {
                    command = "แก้ไข  " + words[0] + " ตัวบน " + " ( " + number + " = " + over_price + " ) " + " และ " + Tokens[1] + " " + words[0] + " บน " + " ( " + number + " = " + tod_price + " )";
                }
                else if (under_price > 0)
                {
                    if (number.Contains('*'))
                    {
                        command = "แก้ไข  3 ตัวล่าง " + " ( " + number + " = " + under_price + " )";
                    }
                    else if (number.Contains('/'))
                    {
                        command = "แก้ไข  2 ตัวล่าง " + " ( " + number + " = " + under_price + " )";
                    }
                    else if (number.Contains('-'))
                    {
                        string numCheck = number.Replace("-", "");
                        command = "แก้ไข  " + result + " ( " + numCheck + " = " + under_price + " )";
                    }
                    else
                    {
                        if (number.Length == 1)
                        {
                            command = "แก้ไข  " + result + " ( " + number + " = " + under_price + " )";
                        }
                        else if (number.Length == 2)
                        {
                            command = "แก้ไข  2 ตัวล่าง" + " ( " + number + " = " + under_price + " )";
                        }
                        else if (number.Length == 3)
                        {
                            command = "แก้ไข  3 ตัวล่าง" + " ( " + number + " = " + under_price + " )";
                        }
                    }
                }
            }
            else if (check == 1)
            {
                if (over_price == 0 && tod_price > 0)
                {
                    if (number.Length == 2)
                    {
                        command = "แก้ไข โต๊ด 2 บน " + " ( " + number + " = " + "-" + tod_price + " ) ";
                    }
                    else if (number.Length == 3)
                    {
                        command = "แก้ไข โต๊ด 3 บน " + " ( " + number + " = " + "-" + tod_price + " ) ";
                    }
                    else if (number.Length == 4)
                    {
                        command = "แก้ไข  พวง 4 ตัว" + " ( " + number + " = " + "-" + tod_price + " ) ";
                    }
                    else if (number.Length == 5)
                    {
                        command = "แก้ไข โต๊ด 5 บนหมู่  " + " ( " + number + " = " + "-" + tod_price + " ) ";
                    }
                }
                else if (over_price > 0 && tod_price == 0)
                {
                    if (number.Length == 2 || number.Length == 2 && number.Contains('/'))
                    {
                        command = "แก้ไข  2 ตัวบน" + " ( " + number + " = " + "-" + over_price + " )";
                    }
                    else if (number.Contains('*'))
                    {
                        string numCheck = number.Replace("*", "");
                        if (numCheck.Length == 2)
                        {
                            command = "แก้ไข  2 ตัวบน, 2 บนคู่หน้า, 2 บนคู่ถ่าง " + " ( " + number + " = " + "-" + over_price + " )";
                        }
                        else if (numCheck.Length == 3 || numCheck.Length == 4 || numCheck.Length == 5)
                        {
                            command = "แก้ไข  3 ตัวบน " + " ( " + number + " = " + "-" + over_price + " )";
                        }
                    }
                    else if (number.Contains('-'))
                    {
                        string numCheck = number.Replace("-", "");

                        if (numCheck.Length == 2)
                        {
                            command = "แก้ไข  " + result + " ( " + number + " = " + "-" + over_price + " )";
                        }
                        else if (numCheck.Length == 1)
                        {
                            command = "แก้ไข  " + result + " ( " + numCheck + " = " + "-" + over_price + " )";
                        }

                    }
                    else
                    {
                        command = "แก้ไข  " + result + " ( " + number + " = " + "-" + over_price + " )";
                    }
                }
                else if (over_price > 0 && tod_price > 0)
                {
                    command = "แก้ไข  " + words[0] + " ตัวบน " + " ( " + number + " = " + "-" + over_price + " ) " + " และ " + Tokens[1] + " " + words[0] + " บน " + " ( " + number + " = " + "-" + tod_price + " )";
                }
                else if (under_price > 0)
                {
                    if (number.Contains('*'))
                    {
                        command = "แก้ไข  3 ตัวล่าง " + " ( " + number + " = " + "-" + under_price + " )";
                    }
                    else if (number.Contains('/'))
                    {
                        command = "แก้ไข  2 ตัวล่าง " + " ( " + number + " = " + "-" + under_price + " )";
                    }
                    else if (number.Contains('-'))
                    {
                        string numCheck = number.Replace("-", "");
                        command = "แก้ไข  " + result + " ( " + numCheck + " = " + "-" + under_price + " )";
                    }
                    else
                    {
                        if (number.Length == 1)
                        {
                            command = "แก้ไข  " + result + " ( " + number + " = " + "-" + under_price + " )";
                        }
                        else if (number.Length == 2)
                        {
                            command = "แก้ไข  2 ตัวล่าง" + " ( " + number + " = " + "-" + under_price + " )";
                        }
                        else if (number.Length == 3)
                        {
                            command = "แก้ไข  3 ตัวล่าง" + " ( " + number + " = " + "-" + under_price + " )";
                        }
                    }
                }
            }

            return command;
        }
        public NumberDTO findNumber(string number, string numberType)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" select ");
            sql.AppendLine(" Number ");
            sql.AppendLine(" ,SUM(Over_Price) Over_Price ");
            sql.AppendLine(" ,SUM(Under_Price) Under_Price ");
            sql.AppendLine(" ,SUM(Tod_price) Tod_price  ");
            sql.AppendLine("  ");
            sql.AppendLine(" from tbl_number WHERE  Number=@Number AND  numType=@numType GROUP BY Number ");

            List<NumberDTO> result = new List<NumberDTO>();

            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(base.CreateParamater("Number", number));
            param.Add(base.CreateParamater("numType", numberType));

            result = this.Context.ExecuteStoreQuery<NumberDTO>(sql.ToString(), param.ToArray()).ToList();
            return result.FirstOrDefault();
        }
        public void updateNum(NumberDTO num, string userLogin)
        {

            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" DELETE tbl_number WHERE  Number=@Number AND  numType=@numType ");
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(base.CreateParamater("Number", num.Number));
            param.Add(base.CreateParamater("numType", num.NumType));
            this.Context.ExecuteStoreCommand(sql.ToString(), param.ToArray());

            tbl_number numberAddDB = new tbl_number()
            {

                CreateBy = userLogin,
                CreateDate = DateTime.Now,
                Number = num.Number,
                NumType = num.NumType,
                Over_Price = num.Over_Price,
                Tod_Price = num.Tod_Price,
                Under_Price = num.Under_Price,
                UpdateBy = userLogin,
                UpdateDate = DateTime.Now
            };
            this.Context.AddTotbl_number(numberAddDB);
            this.Context.SaveChanges();
        }

        public int ImportFile(List<NumberDTO> addNumber, string UserName)
        {
            int result = 0;
            DateTime dateGoble = GetDateGobal();
            TransactionOptions tranOpt = new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted };
            //using (var trans = new TransactionScope(TransactionScopeOption.Required, tranOpt))
            //{
            try
            {
                #region Transaction Scope
                foreach (NumberDTO num in addNumber)
                {

                    tbl_number numberAddDB = new tbl_number()
                    {

                        CreateBy = UserName,
                        CreateDate = dateGoble,
                        Number = num.Number,
                        NumType = num.NumType,
                        Over_Price = num.Over_Price,
                        Tod_Price = num.Tod_Price,
                        Under_Price = num.Under_Price,
                        UpdateBy = UserName,
                        UpdateDate = dateGoble
                    };
                    this.Context.AddTotbl_number(numberAddDB);
                    result += 1;

                }
                this.Context.SaveChanges();
                // trans.Complete();
                #endregion
            }
            catch (Exception ex)
            {
                throw ex;

            }
            //}
            return result;
        }
        public List<NumberDTO> GetX19(NumberDTO number)
        {
            List<NumberDTO> result = new List<NumberDTO>();
            string numCheck = number.Number.Replace("/", "");
            NumberDTO numAdd;
            for (int i = 0; i < 10; i++)
            {
                if (i != int.Parse(numCheck))
                {
                    numAdd = new NumberDTO();
                    numAdd.Number = numCheck + i.ToString();
                    numAdd.Over_Price = number.Over_Price;
                    numAdd.Under_Price = number.Under_Price;
                    numAdd.Tod_Price = number.Tod_Price;
                    result.Add(numAdd);

                    numAdd = new NumberDTO();
                    numAdd.Number = i.ToString() + numCheck;
                    numAdd.Over_Price = number.Over_Price;
                    numAdd.Under_Price = number.Under_Price;
                    numAdd.Tod_Price = number.Tod_Price;
                    result.Add(numAdd);
                }
            }
            numAdd = new NumberDTO();
            numAdd.Number = numCheck + numCheck;
            numAdd.Over_Price = number.Over_Price;
            numAdd.Under_Price = number.Under_Price;
            numAdd.Tod_Price = number.Tod_Price;
            result.Add(numAdd);
            return result;


        }
        public List<NumberDTO> SwitchNum_star(NumberDTO number)
        {
            List<NumberDTO> result = new List<NumberDTO>();
            string numCheck = number.Number.Replace("*", "");
            NumberDTO numAdd;
            int orginalLength = numCheck.Length;
            Permute p = new Permute();
            if (numCheck.Length == 2)
            {
                numCheck = numCheck + "-";
            }
            string c = numCheck;

            char[] c2 = c.ToCharArray();

            /*calling the permute*/

            p.setper(c2);

            foreach (string item in p.result)
            {
                string str3digi = item.Substring(0, 3);
                var check = from num in result
                            where num.Number == str3digi
                            select num;
                if (check.Count() == 0)
                {
                    if (orginalLength == 2 && str3digi[0] == '-')
                    {
                        str3digi = str3digi.Replace("-", "");
                    }
                    numAdd = new NumberDTO();
                    numAdd.NumType = number.NumType;
                    numAdd.Number = str3digi;
                    numAdd.Over_Price = number.Over_Price;
                    numAdd.Under_Price = number.Under_Price;
                    numAdd.Tod_Price = number.Tod_Price;
                    result.Add(numAdd);
                }
            }

            #region :: Old Code ::
            //switch (numCheck.Length)
            //{
            //    case 2:
            //        if (numCheck.GroupBy(s => s).Count() == 1)
            //        {

            //            #region in case 22
            //            //add  22
            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = numCheck;
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);

            //            //add 22-
            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = numCheck + "-";
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);

            //            //add 2-2
            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = numCheck[0] + "-" + numCheck[1];
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);

            //            #endregion
            //        }
            //        else
            //        {
            //            #region in case 12

            //            //add  12
            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = numCheck;
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);

            //            //add  21
            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = numCheck[1].ToString() + numCheck[0].ToString();
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);


            //            //add 12-
            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = numCheck + "-";
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);

            //            //add 21-
            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = numCheck[1].ToString() + numCheck[0].ToString() + "-";
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);


            //            //add 1-2
            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = numCheck[0] + "-" + numCheck[1];
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);


            //            //add 2-1
            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = numCheck[1] + "-" + numCheck[0];
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);
            //            #endregion
            //        }
            //        break;
            //    case 3:
            //        if (numCheck.GroupBy(s => s).Count() == 2)
            //        {

            //            #region in case 122 221 212

            //            result.AddRange(GetX3(number, numCheck));

            //            #endregion

            //        }
            //        else
            //        {
            //            #region in case 123 321 213 312 231 132

            //            result.AddRange(GetNumx6(number, numCheck));
            //            #endregion
            //        }
            //        break;
            //    case 4:

            //        if (numCheck.GroupBy(s => s).Count() == 4)
            //        {
            //            #region in case 1234 : 123, 124, 134, 234
            //            string num1 = numCheck[0].ToString();
            //            string num2 = numCheck[1].ToString();
            //            string num3 = numCheck[2].ToString();
            //            string num4 = numCheck[3].ToString();
            //            result.AddRange(GetNumx6(number, num1 + num2 + num3));
            //            result.AddRange(GetNumx6(number, num1 + num2 + num4));
            //            result.AddRange(GetNumx6(number, num1 + num3 + num4));
            //            result.AddRange(GetNumx6(number, num2 + num3 + num4));
            //            #endregion

            //        }
            //        else if (numCheck.GroupBy(s => s).Count() == 3)
            //        {
            //            #region in case 1233 : 123, 233, 133

            //            var sr = numCheck.GroupBy(s => s).ToList();
            //            string keyDup;
            //            string nonDup1;
            //            string nonDup2;
            //            if (numCheck.Where(s => s == sr[0].Key.ToString().ToCharArray()[0]).ToList().Count() >= 2)
            //            {
            //                keyDup = sr[0].Key.ToString();
            //                nonDup1 = sr[1].Key.ToString();
            //                nonDup2 = sr[2].Key.ToString();
            //            }
            //            else if (numCheck.Where(s => s == sr[1].Key.ToString().ToCharArray()[0]).ToList().Count() >= 2)
            //            {
            //                keyDup = sr[1].Key.ToString();
            //                nonDup1 = sr[0].Key.ToString();
            //                nonDup2 = sr[2].Key.ToString();
            //            }
            //            else
            //            {
            //                keyDup = sr[2].Key.ToString();
            //                nonDup1 = sr[0].Key.ToString();
            //                nonDup2 = sr[1].Key.ToString();
            //            }

            //            result.AddRange(GetNumx6(number, nonDup1 + nonDup2 + keyDup));
            //            result.AddRange(GetNumx6(number, nonDup1 + keyDup + keyDup));
            //            result.AddRange(GetNumx6(number, nonDup2 + keyDup + keyDup));


            //            #endregion
            //        }
            //        else if (numCheck.GroupBy(s => s).Count() == 2)
            //        {
            //            #region in case 1333 : 133, 333

            //            var sr = numCheck.GroupBy(s => s).ToList();
            //            string keyDup;
            //            string nonDup;
            //            if (numCheck.Where(s => s == sr[0].Key.ToString().ToCharArray()[0]).ToList().Count() >= 2)
            //            {
            //                keyDup = sr[0].Key.ToString();
            //                nonDup = sr[1].Key.ToString();
            //            }
            //            else
            //            {
            //                keyDup = sr[1].Key.ToString();
            //                nonDup = sr[0].Key.ToString();
            //            }

            //            result.AddRange(GetX3(number, nonDup + keyDup + keyDup));

            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = keyDup + keyDup + keyDup;
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);

            //            #endregion
            //        }
            //        else
            //        {
            //            throw new Exception("ไม่สามารถกลับเลข ซ้ำกัน 4 ตัวได้");
            //        }



            //        break;
            //    case 5:
            //        if (numCheck.GroupBy(s => s).Count() == 5)
            //        {
            //            #region in case 12345 :123,124,125,234,235,345,341,451,452,513
            //            string num1 = numCheck[0].ToString();
            //            string num2 = numCheck[1].ToString();
            //            string num3 = numCheck[2].ToString();
            //            string num4 = numCheck[3].ToString();
            //            string num5 = numCheck[4].ToString();

            //            result.AddRange(GetNumx6(number, num1 + num2 + num3));
            //            result.AddRange(GetNumx6(number, num1 + num2 + num4));
            //            result.AddRange(GetNumx6(number, num1 + num2 + num5));
            //            result.AddRange(GetNumx6(number, num2 + num3 + num4));
            //            result.AddRange(GetNumx6(number, num2 + num3 + num5));
            //            result.AddRange(GetNumx6(number, num3 + num4 + num5));
            //            result.AddRange(GetNumx6(number, num3 + num4 + num1));
            //            result.AddRange(GetNumx6(number, num4 + num5 + num1));
            //            result.AddRange(GetNumx6(number, num4 + num5 + num2));
            //            result.AddRange(GetNumx6(number, num5 + num1 + num3));

            //            #endregion
            //        }
            //        else if (numCheck.GroupBy(s => s).Count() == 4)
            //        {

            //            #region in case 12344 : 123, 124, 134, 234,144,244,344

            //            var sr = numCheck.GroupBy(s => s).ToList();
            //            string keyDup;
            //            string nonDup1;
            //            string nonDup2;
            //            string nonDup3;
            //            if (numCheck.Where(s => s == sr[0].Key.ToString().ToCharArray()[0]).ToList().Count() >= 2)
            //            {
            //                keyDup = sr[0].Key.ToString();
            //                nonDup1 = sr[1].Key.ToString();
            //                nonDup2 = sr[2].Key.ToString();
            //                nonDup3 = sr[3].Key.ToString();
            //            }
            //            else if (numCheck.Where(s => s == sr[1].Key.ToString().ToCharArray()[0]).ToList().Count() >= 2)
            //            {
            //                keyDup = sr[1].Key.ToString();
            //                nonDup1 = sr[0].Key.ToString();
            //                nonDup2 = sr[2].Key.ToString();
            //                nonDup3 = sr[3].Key.ToString();
            //            }
            //            else if (numCheck.Where(s => s == sr[2].Key.ToString().ToCharArray()[0]).ToList().Count() >= 2)
            //            {
            //                keyDup = sr[2].Key.ToString();
            //                nonDup1 = sr[0].Key.ToString();
            //                nonDup2 = sr[1].Key.ToString();
            //                nonDup3 = sr[3].Key.ToString();
            //            }
            //            else
            //            {
            //                keyDup = sr[3].Key.ToString();
            //                nonDup1 = sr[0].Key.ToString();
            //                nonDup2 = sr[1].Key.ToString();
            //                nonDup3 = sr[2].Key.ToString();
            //            }

            //            result.AddRange(GetNumx6(number, nonDup1 + nonDup2 + nonDup3));
            //            result.AddRange(GetNumx6(number, nonDup1 + nonDup2 + keyDup));
            //            result.AddRange(GetNumx6(number, nonDup1 + nonDup3 + keyDup));
            //            result.AddRange(GetNumx6(number, nonDup2 + nonDup3 + keyDup));

            //            result.AddRange(GetX3(number, nonDup1 + keyDup + keyDup));
            //            result.AddRange(GetX3(number, nonDup2 + keyDup + keyDup));
            //            result.AddRange(GetX3(number, nonDup3 + keyDup + keyDup));
            //            #endregion
            //        }
            //        else if (numCheck.GroupBy(s => s).Count() == 3)
            //        {

            //            #region in case 12333:123,233,133,333

            //            var sr = numCheck.GroupBy(s => s).ToList();
            //            string keyDup;
            //            string nonDup1;
            //            string nonDup2;

            //            if (numCheck.Where(s => s == sr[0].Key.ToString().ToCharArray()[0]).ToList().Count() >= 2)
            //            {
            //                keyDup = sr[0].Key.ToString();
            //                nonDup1 = sr[1].Key.ToString();
            //                nonDup2 = sr[2].Key.ToString();

            //            }
            //            else if (numCheck.Where(s => s == sr[1].Key.ToString().ToCharArray()[0]).ToList().Count() >= 2)
            //            {
            //                keyDup = sr[1].Key.ToString();
            //                nonDup1 = sr[0].Key.ToString();
            //                nonDup2 = sr[2].Key.ToString();

            //            }
            //            else
            //            {
            //                keyDup = sr[2].Key.ToString();
            //                nonDup1 = sr[0].Key.ToString();
            //                nonDup2 = sr[1].Key.ToString();

            //            }
            //            result.AddRange(GetNumx6(number, nonDup1 + nonDup2 + keyDup));

            //            result.AddRange(GetX3(number, nonDup1 + keyDup + keyDup));
            //            result.AddRange(GetX3(number, nonDup2 + keyDup + keyDup));

            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = keyDup + keyDup + keyDup;
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);
            //            #endregion
            //        }
            //        else if (numCheck.GroupBy(s => s).Count() == 2)
            //        {
            //            #region in case 13333,133,333
            //            var sr = numCheck.GroupBy(s => s).ToList();
            //            string keyDup;
            //            string nonDup;
            //            if (numCheck.Where(s => s == sr[0].Key.ToString().ToCharArray()[0]).ToList().Count() >= 2)
            //            {
            //                keyDup = sr[0].Key.ToString();
            //                nonDup = sr[1].Key.ToString();
            //            }
            //            else
            //            {
            //                keyDup = sr[1].Key.ToString();
            //                nonDup = sr[0].Key.ToString();
            //            }

            //            result.AddRange(GetX3(number, nonDup + keyDup + keyDup));

            //            numAdd = new NumberDTO();
            //            numAdd.NumType = number.NumType;
            //            numAdd.Number = keyDup + keyDup + keyDup;
            //            numAdd.Over_Price = number.Over_Price;
            //            numAdd.Under_Price = number.Under_Price;
            //            numAdd.Tod_Price = number.Tod_Price;
            //            result.Add(numAdd);
            //            #endregion
            //        }
            //        else
            //        {
            //            throw new Exception("ไม่สามารถกลับเลข ซ้ำกัน 5 ตัวได้");
            //        }
            //        break;

            //}
            #endregion

            return result;
        }

        #region :: Old Code
        //private static List<NumberDTO> GetX3(NumberDTO number, string numCheck)
        //{
        //    List<NumberDTO> result = new List<NumberDTO>();
        //    NumberDTO numAdd;

        //    var sr = numCheck.GroupBy(s => s).ToList();
        //    string keyDup;
        //    string nonDup;
        //    if (numCheck.Where(s => s == sr[0].Key.ToString().ToCharArray()[0]).ToList().Count() == 2)
        //    {
        //        keyDup = sr[0].Key.ToString();
        //        nonDup = sr[1].Key.ToString();
        //    }
        //    else
        //    {
        //        keyDup = sr[1].Key.ToString();
        //        nonDup = sr[0].Key.ToString();
        //    }
        //    //add  122
        //    numAdd = new NumberDTO();
        //    numAdd.NumType = number.NumType;
        //    numAdd.Number = nonDup + keyDup + keyDup;
        //    numAdd.Over_Price = number.Over_Price;
        //    numAdd.Under_Price = number.Under_Price;
        //    numAdd.Tod_Price = number.Tod_Price;
        //    result.Add(numAdd);


        //    //add  221
        //    numAdd = new NumberDTO();
        //    numAdd.NumType = number.NumType;
        //    numAdd.Number = keyDup + nonDup + keyDup;
        //    numAdd.Over_Price = number.Over_Price;
        //    numAdd.Under_Price = number.Under_Price;
        //    numAdd.Tod_Price = number.Tod_Price;
        //    result.Add(numAdd);

        //    //add  212
        //    numAdd = new NumberDTO();
        //    numAdd.NumType = number.NumType;
        //    numAdd.Number = keyDup + keyDup + nonDup;
        //    numAdd.Over_Price = number.Over_Price;
        //    numAdd.Under_Price = number.Under_Price;
        //    numAdd.Tod_Price = number.Tod_Price;
        //    result.Add(numAdd);
        //    return result;
        //}

        //private static List<NumberDTO> GetNumx6(NumberDTO number, string numCheck)
        //{
        //    List<NumberDTO> result = new List<NumberDTO>();
        //    NumberDTO numAdd;
        //    string str1 = numCheck[0].ToString();
        //    string str2 = numCheck[1].ToString();
        //    string str3 = numCheck[2].ToString();

        //    //add  123
        //    numAdd = new NumberDTO();
        //    numAdd.NumType = number.NumType;
        //    numAdd.Number = numCheck;
        //    numAdd.Over_Price = number.Over_Price;
        //    numAdd.Under_Price = number.Under_Price;
        //    numAdd.Tod_Price = number.Tod_Price;
        //    result.Add(numAdd);

        //    //add  321
        //    numAdd = new NumberDTO();
        //    numAdd.NumType = number.NumType;
        //    numAdd.Number = str3 + str2 + str1;
        //    numAdd.Over_Price = number.Over_Price;
        //    numAdd.Under_Price = number.Under_Price;
        //    numAdd.Tod_Price = number.Tod_Price;
        //    result.Add(numAdd);

        //    //add  213
        //    numAdd = new NumberDTO();
        //    numAdd.NumType = number.NumType;
        //    numAdd.Number = str2 + str1 + str3;
        //    numAdd.Over_Price = number.Over_Price;
        //    numAdd.Under_Price = number.Under_Price;
        //    numAdd.Tod_Price = number.Tod_Price;
        //    result.Add(numAdd);

        //    //add  312
        //    numAdd = new NumberDTO();
        //    numAdd.NumType = number.NumType;
        //    numAdd.Number = str3 + str1 + str2;
        //    numAdd.Over_Price = number.Over_Price;
        //    numAdd.Under_Price = number.Under_Price;
        //    numAdd.Tod_Price = number.Tod_Price;
        //    result.Add(numAdd);

        //    //add  231
        //    numAdd = new NumberDTO();
        //    numAdd.NumType = number.NumType;
        //    numAdd.Number = str2 + str3 + str1;
        //    numAdd.Over_Price = number.Over_Price;
        //    numAdd.Under_Price = number.Under_Price;
        //    numAdd.Tod_Price = number.Tod_Price;
        //    result.Add(numAdd);

        //    //add  132
        //    numAdd = new NumberDTO();
        //    numAdd.NumType = number.NumType;
        //    numAdd.Number = str1 + str3 + str2;
        //    numAdd.Over_Price = number.Over_Price;
        //    numAdd.Under_Price = number.Under_Price;
        //    numAdd.Tod_Price = number.Tod_Price;
        //    result.Add(numAdd);
        //    return result;
        //}
        #endregion

        public List<NumberDTO> getAllnumber(bool isOver)
        {
            List<NumberDTO> result = new List<NumberDTO>();

            if (!isOver)
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine(" select ");
                sql.AppendLine(" Number ");
                sql.AppendLine(" ,SUM(Over_Price) Over_Price ");
                sql.AppendLine(" ,SUM(Under_Price) Under_Price ");
                sql.AppendLine(" ,SUM(Tod_price) Tod_price  ");
                sql.AppendLine(" ,NumType NumType ");
                sql.AppendLine(" from tbl_number  GROUP BY Number ,NumType ");

                List<SqlParameter> param = new List<SqlParameter>();
                result = this.Context.ExecuteStoreQuery<NumberDTO>(sql.ToString(), param.ToArray()).ToList();
            }
            else
            {
                result = Getnum();
            }
            return result;
        }
        public List<NumberDTO> ConvetNumberShow(List<NumberDTO> db, string numType, decimal limit, decimal OverLimitSt, decimal OverLimitEn)
        {
            List<NumberDTO> result = new List<NumberDTO>();
            NumberDTO numShow;
            foreach (NumberDTO item in db)
            {
                numShow = new NumberDTO()
                {
                    Number = item.Number,
                    Over_Price = 0,
                    Under_Price = 0,
                    Tod_Price = 0

                };



                switch (numType)
                {
                    case "O":
                        if (item.Over_Price > limit)
                        {
                            numShow.Over_Price = item.Over_Price - limit;
                        }
                        break;
                    case "U":
                        if (item.Under_Price > limit)
                        {
                            numShow.Under_Price = item.Under_Price - limit;
                        }
                        break;
                    case "T":
                        if (item.Tod_Price > limit)
                        {
                            numShow.Tod_Price = item.Tod_Price - limit;
                        }
                        break;
                    case "":
                        if (item.Tod_Price > limit)
                        {
                            numShow.Tod_Price = item.Tod_Price - limit;
                        }
                        if (item.Over_Price > limit)
                        {
                            numShow.Over_Price = item.Over_Price - limit;
                        }
                        if (item.Under_Price > limit)
                        {
                            numShow.Under_Price = item.Under_Price - limit;
                        }
                        break;
                }
                if (numType == "O" || numType == "T" || numType == "")
                {
                    numShow.NumType = Enum.NumberType.NumberOverAdd;
                }
                else
                {
                    numShow.NumType = Enum.NumberType.NumberUnderAdd;
                }
                if (numShow.Over_Price + numShow.Under_Price + numShow.Tod_Price > 0)
                {
                    result.Add(numShow);
                }

            }

            return result;

        }

        protected List<NumberDTO> Getnum()
        {
            string NumberType = Enum.NumberType.Over3;
            tbl_PayService PayService = new tbl_PayService();
            Pay limit;
            List<NumberDTO> numberDTO = new List<NumberDTO>();
            List<NumberDTO> result = new List<NumberDTO>();

            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "O", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);

            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.Tod3;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "T", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);


            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.Under3;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "U", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);


            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.Over2;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "O", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);

            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.Under2;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "U", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);

            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.Tod2;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "T", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);

            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.Over1;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "O", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);

            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.Under1;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "U", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);

            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.TwinFront;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);

            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.TwinStretch;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);


            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.FourNumber;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);

            numberDTO = new List<NumberDTO>();
            NumberType = Enum.NumberType.FiveNumber;
            numberDTO = get_numberShow(NumberType);
            limit = PayService.GetLimit(NumberType);
            numberDTO = ConvetNumberShow(numberDTO, "", limit.LimitBuy, 0, 0);
            result.AddRange(numberDTO);

            return result;

        }
        public List<NumberDTO> get_numberShow(string NumberType)
        {

            List<NumberDTO> result = new List<NumberDTO>();
            string having = string.Empty;
            string where = string.Empty;
            switch (NumberType)
            {
                case Enum.NumberType.Over3:
                    where = " WHERE LEN(Number)=3 AND NOT(Number LIKE '%-%') AND NumType='NO' ";
                    having = " HAVING SUM(Over_Price) >0 ";
                    break;
                case Enum.NumberType.Tod3:
                    where = " WHERE LEN(Number)=3 AND NOT(Number LIKE '%-%') AND NumType='NO' ";
                    having = " HAVING SUM(Tod_price) >0 ";
                    break;
                case Enum.NumberType.Over2:
                    where = " WHERE LEN(Number)=2 AND NOT(Number LIKE '%-%') AND NumType='NO' ";
                    having = " HAVING SUM(Over_Price) >0 ";
                    break;
                case Enum.NumberType.Tod2:
                    where = " WHERE LEN(Number)=2 AND NOT(Number LIKE '%-%') AND NumType='NO' ";
                    having = " HAVING SUM(Tod_price) >0 ";
                    break;
                case Enum.NumberType.Under3:
                    where = " WHERE LEN(Number)=3 AND NOT(Number LIKE '%-%') AND NumType='NU' ";
                    having = " HAVING SUM(Under_Price) >0 ";
                    break;
                case Enum.NumberType.Under2:
                    where = " WHERE LEN(Number)=2 AND NOT(Number LIKE '%-%') AND NumType='NU' ";
                    having = " HAVING SUM(Under_Price) >0 ";
                    break;
                case Enum.NumberType.TwinFront:
                    where = " WHERE LEN(Number)=3 AND Number LIKE '%-' AND NOT(Number LIKE '%--') AND NumType='NO' ";
                    having = " HAVING SUM(Over_Price) >0 OR  SUM(Under_Price) >0 OR  SUM(Tod_price) >0 ";
                    break;
                case Enum.NumberType.TwinStretch:
                    where = " WHERE LEN(Number)=3 AND Number LIKE '%-%' AND NOT(Number LIKE '-%') AND NOT(Number LIKE '%-') AND NumType='NO' ";
                    having = " HAVING SUM(Over_Price) >0 OR  SUM(Under_Price) >0 OR  SUM(Tod_price) >0 ";
                    break;
                case Enum.NumberType.Under1:
                    where = " WHERE LEN(Number)=1 AND NumType='NU' ";
                    having = " HAVING SUM(Under_Price) >0 ";
                    break;
                case Enum.NumberType.Under1Decimal:
                    where = " WHERE LEN(Number)=2 AND Number LIKE '%-'  AND NumType='NU' ";
                    having = " HAVING SUM(Under_Price) >0 ";
                    break;
                case Enum.NumberType.Under1unit:
                    where = " WHERE LEN(Number)=2 AND Number LIKE '-%'  AND NumType='NU' ";
                    having = " HAVING SUM(Under_Price) >0 ";
                    break;
                case Enum.NumberType.Over1:
                    where = " WHERE LEN(Number)=1  AND NumType='NO' ";
                    having = " HAVING SUM(Over_Price) >0 ";
                    break;
                case Enum.NumberType.Over1Unit:
                    where = " WHERE LEN(Number)=3 AND NumType='NO' AND Number LIKE '--%'  ";
                    having = " HAVING SUM(Over_Price) >0 ";
                    break;
                case Enum.NumberType.Over1Decimal:
                    where = " WHERE LEN(Number)=3 AND NumType='NO' AND Number LIKE '-%-'  ";
                    having = " HAVING SUM(Over_Price) >0 ";
                    break;
                case Enum.NumberType.Over1Hundet:
                    where = " WHERE LEN(Number)=3 AND NumType='NO' AND Number LIKE '%--'  ";
                    having = " HAVING SUM(Over_Price) >0 ";
                    break;
                case Enum.NumberType.FourNumber:
                    where = "  WHERE LEN(Number)=4  AND NumType='NO'  ";
                    having = " HAVING SUM(Over_Price) >0 OR  SUM(Under_Price) >0 OR  SUM(Tod_price) >0 ";
                    break;
                case Enum.NumberType.FiveNumber:
                    where = "  WHERE LEN(Number)=5 AND NumType='NO'  ";
                    having = " HAVING SUM(Over_Price) >0 OR  SUM(Under_Price) >0 OR  SUM(Tod_price) >0 ";
                    break;
            }
            string sql = getSQLSelectNumber(having, where);
            result = this.Context.ExecuteStoreQuery<NumberDTO>(sql).ToList();
            List<NumberDTO> todList = new List<NumberDTO>();
            foreach (NumberDTO todNum in result.OrderBy(n => n.Number))
            {
                if (todNum.Tod_Price > 0)
                {


                    #region :: Tod Solution Cal ::

                    #region :: Num 2 ::
                    if (todNum.Number.Length == 2)
                    {
                        var queryTod = from t in todList
                                       where t.Number.Contains(todNum.Number[0])
                                       && t.Number.Contains(todNum.Number[1])
                                       select t;
                        if (queryTod.Count() > 0)
                        {
                            string minTod = queryTod.Min(t => t.Number);
                            NumberDTO tempTpd = todList.Where(n => n.Number == minTod).FirstOrDefault();
                            tempTpd.Tod_Price += todNum.Tod_Price;
                        }
                        else
                        {
                            NumberDTO addNum = new NumberDTO()
                            {
                                CreateBy = todNum.CreateBy,
                                CreateDate = todNum.CreateDate,
                                Cut_Out = todNum.Cut_Out,
                                Number = todNum.Number,
                                NumType = todNum.NumType,
                                Tod_Price = todNum.Tod_Price,
                                UpdateBy = todNum.UpdateBy,
                                UpdateDate = todNum.UpdateDate
                            };
                            todList.Add(addNum);
                        }

                    }
                    #endregion

                    #region :: Num 3 ::
                    if (todNum.Number.Length == 3)
                    {
                        var queryTod = from t in todList
                                       where t.Number.Contains(todNum.Number[0])
                                       && t.Number.Contains(todNum.Number[1])
                                       && t.Number.Contains(todNum.Number[2])
                                       select t;
                        if (queryTod.Count() > 0)
                        {
                            string minTod = queryTod.Min(t => t.Number);
                            NumberDTO tempTpd = todList.Where(n => n.Number == minTod).FirstOrDefault();
                            tempTpd.Tod_Price += todNum.Tod_Price;
                        }
                        else
                        {
                            NumberDTO addNum = new NumberDTO()
                            {
                                CreateBy = todNum.CreateBy,
                                CreateDate = todNum.CreateDate,
                                Cut_Out = todNum.Cut_Out,
                                Number = todNum.Number,
                                NumType = todNum.NumType,
                                Tod_Price = todNum.Tod_Price,
                                UpdateBy = todNum.UpdateBy,
                                UpdateDate = todNum.UpdateDate
                            };
                            todList.Add(addNum);
                        }

                    }
                    #endregion

                    #region :: Num 4 ::
                    if (todNum.Number.Length == 4)
                    {
                        var queryTod = from t in todList
                                       where t.Number.Contains(todNum.Number[0])
                                       && t.Number.Contains(todNum.Number[1])
                                       && t.Number.Contains(todNum.Number[2])
                                        && t.Number.Contains(todNum.Number[3])
                                       select t;
                        if (queryTod.Count() > 0)
                        {
                            string minTod = queryTod.Min(t => t.Number);
                            NumberDTO tempTpd = todList.Where(n => n.Number == minTod).FirstOrDefault();
                            tempTpd.Tod_Price += todNum.Tod_Price;
                        }
                        else
                        {
                            NumberDTO addNum = new NumberDTO()
                            {
                                CreateBy = todNum.CreateBy,
                                CreateDate = todNum.CreateDate,
                                Cut_Out = todNum.Cut_Out,
                                Number = todNum.Number,
                                NumType = todNum.NumType,
                                Tod_Price = todNum.Tod_Price,
                                UpdateBy = todNum.UpdateBy,
                                UpdateDate = todNum.UpdateDate
                            };
                            todList.Add(addNum);
                        }

                    }
                    #endregion

                    #region :: Num 5 ::
                    if (todNum.Number.Length == 5)
                    {
                        var queryTod = from t in todList
                                       where t.Number.Contains(todNum.Number[0])
                                       && t.Number.Contains(todNum.Number[1])
                                       && t.Number.Contains(todNum.Number[2])
                                        && t.Number.Contains(todNum.Number[3])
                                         && t.Number.Contains(todNum.Number[4])
                                       select t;
                        if (queryTod.Count() > 0)
                        {
                            string minTod = queryTod.Min(t => t.Number);
                            NumberDTO tempTpd = todList.Where(n => n.Number == minTod).FirstOrDefault();
                            tempTpd.Tod_Price += todNum.Tod_Price;
                        }
                        else
                        {
                            NumberDTO addNum = new NumberDTO()
                            {
                                CreateBy = todNum.CreateBy,
                                CreateDate = todNum.CreateDate,
                                Cut_Out = todNum.Cut_Out,
                                Number = todNum.Number,
                                NumType = todNum.NumType,
                                Tod_Price = todNum.Tod_Price,
                                UpdateBy = todNum.UpdateBy,
                                UpdateDate = todNum.UpdateDate
                            };
                            todList.Add(addNum);
                        }

                    }
                    #endregion

                    #endregion

                    todNum.Tod_Price = 0;
                }
            }
            foreach (NumberDTO todNum in todList)
            {
                var query = from n in result
                            where
                                n.Number == todNum.Number
                            select n;
                NumberDTO temp = query.FirstOrDefault();
                if (temp != null)
                {
                    temp.Tod_Price = todNum.Tod_Price;
                }
            }
            return result;
        }
        public List<NumberDTO> get_3Over_number()
        {
            string condition = string.Format("having MAX(NumType)='{0}' ", Enum.NumberType.Over3);
            string sql = getSQLSelectNumber(condition, "");

            List<NumberDTO> result = this.Context.ExecuteStoreQuery<NumberDTO>(sql).ToList();
            return result.ToList();
        }
        public string getSQLSelectNumber(string conditionHaving, string conditionWhere)
        {

            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" select ");
            sql.AppendLine(" Number,NumType ");
            sql.AppendLine(" ,SUM(Over_Price) Over_Price ");
            sql.AppendLine(" ,SUM(Under_Price) Under_Price ");
            sql.AppendLine(" ,SUM(Tod_price) Tod_price  ");
            sql.AppendLine(" from tbl_number WITH(NOLOCK)");
            sql.AppendLine(conditionWhere);
            sql.AppendLine(" GROUP BY Number,NumType ");
            sql.AppendLine(conditionHaving);
            sql.AppendLine(" ORDER BY MAX(UpdateDate) DESC");
            return sql.ToString();
        }
        public List<NumberDTO> get_Number_Over()
        {
            string condition = "HAVING SUM(Over_Price)>0 ";
            string sql = getSQLSelectNumber(condition, "");

            List<NumberDTO> result = this.Context.ExecuteStoreQuery<NumberDTO>(sql).ToList();
            return result.ToList();
        }

        public void DeleteNumber(ShowNumberDTO number, string numberType, string UserName)
        {

            string where = string.Empty;
            if (number == null) { return; }
            if (number.Number == null || number.Number == string.Empty) { return; }
            switch (numberType)
            {
                case Enum.NumberType.Over3:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Over = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.Tod3:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Tod = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.Over2:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Over = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.Tod2:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Tod = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.Under3:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NU' ", number.Number);
                    number.Under = number.Price;
                    numberType = "NU";
                    break;
                case Enum.NumberType.Under2:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NU' ", number.Number);
                    number.Under = number.Price;
                    numberType = "NU";
                    break;
                case Enum.NumberType.TwinFront:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Over = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.TwinStretch:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Over = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.Under1:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NU' ", number.Number);
                    number.Under = number.Price;
                    break;
                case Enum.NumberType.Under1Decimal:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NU' ", number.Number);
                    number.Under = number.Price;
                    numberType = "NU";
                    break;
                case Enum.NumberType.Under1unit:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NU' ", number.Number);
                    number.Under = number.Price;
                    numberType = "NU";
                    break;
                case Enum.NumberType.Over1:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Over = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.Over1Unit:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Over = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.Over1Decimal:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Over = number.Price;
                    break;
                case Enum.NumberType.Over1Hundet:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Over = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.FourNumber:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Tod = number.Price;
                    numberType = "NO";
                    break;
                case Enum.NumberType.FiveNumber:
                    where = string.Format(" WHERE Number='{0}' AND NumType='NO' ", number.Number);
                    number.Tod = number.Price;
                    numberType = "NO";
                    break;
            }

            TransactionOptions tranOpt = new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted };
            using (var trans = new TransactionScope(TransactionScopeOption.Required, tranOpt))
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine("DELETE FROM tbl_number ");
                sql.AppendLine(where);
                this.Context.ExecuteStoreCommand(sql.ToString());
                this.Addnumber(number.Number, number.Over, number.Under, number.Tod, UserName, numberType, false);
                trans.Complete();
            }

        }
    }
}
